package models;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import org.apache.struts.upload.FormFile;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.uploadfileform;

public class PrintoutModel {
	
	private static String insertNewPrintoutStr = "INSERT INTO Printouts (title, file, category) VALUES (?, ?, ?)";
	
	private static String selectCategoryIdStr = "SELECT id FROM Categories WHERE name = ?";
	
	private static String updatePrintoutStr = "UPDATE students SET admission_status = ? WHERE id = ?";
	
	public static void insertPrintout(uploadfileform form) 
		throws DbException {
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			int categoryId = 1;
			PreparedStatement pstmt = conn.prepareStatement(selectCategoryIdStr);
			
			pstmt.setString(1, form.getFirstArea());
			
			ResultSet rset = pstmt.executeQuery();
			
			while (	rset.next() ) {
				categoryId = rset.getInt("id");
			}
			
			pstmt.close();
			rset.close();
			
			pstmt = conn.prepareStatement(insertNewPrintoutStr);
			
			//university, discipline, grad_month, grad_year, gpa, title, student
			pstmt.setString(1, form.getFilename());
			FormFile transcript  = form.getFile();
			pstmt.setBinaryStream(2, transcript.getInputStream(), transcript.getFileSize());
			pstmt.setInt(3, categoryId);
			
			pstmt.executeUpdate();
			pstmt.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		} catch (IOException ex) {
			throw new DbException(ex);
		}
	}
		
}